package server.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

public class DatabaseManager
{
	private static final Logger log = Logger.getLogger(DatabaseManager.class);

	private static final String DATABASE_URL = "jdbc:mysql://localhost:3306";
	private static final String DATABASE_USER = "ytong";
	private static final String DATABASE_PASSWORD = "1234qwer";
	private static final String DATABASE_NAME = "Ytong";

	private Connection connection = null;

	public DatabaseManager() throws Exception
	{
		init();
	}

	private void init() throws Exception
	{
		log.info("Initializing database");

		try
		{
			Class.forName("com.mysql.jdbc.Driver");
		} catch (Exception e)
		{
			throw new Exception("Unable to instantiate JDBC driver: "
					+ e.getMessage());
		}
		connection = DriverManager.getConnection(DATABASE_URL, DATABASE_USER, DATABASE_PASSWORD);

		createDatabase();
	}

	private void createDatabase() throws SQLException
	{
		Statement statement = connection.createStatement();
		statement.execute("CREATE DATABASE IF NOT EXISTS " + DATABASE_NAME);
		statement.execute("USE " + DATABASE_NAME);
		statement.close();
	}

	public void closeConnection()
	{
		try
		{
			log.info("Closing database connection");
			connection.close();
		} catch (SQLException e)
		{
			log.error("Closing the database failed: " + e.getMessage());
		}
	}

	public void update(String sQuery) throws DatabaseException
	{
		if (log.isDebugEnabled())
		{
			log.debug("performing query: " + sQuery);
		}

		Statement statement = null;

		try
		{
			statement = connection.createStatement();
			statement.executeUpdate(sQuery);
			statement.close();
		} catch (SQLException e)
		{
			throw new DatabaseException(e.getMessage());
		}
	}

	public void execute(String sQuery) throws DatabaseException
	{
		if (log.isDebugEnabled())
		{
			log.debug("performing query: " + sQuery);
		}

		Statement statement = null;

		try
		{
			statement = connection.createStatement();
			statement.execute(sQuery);
			statement.close();
		} catch (SQLException e)
		{
			throw new DatabaseException(e.getMessage());
		}
	}

	public int addAutoIncrement(String sQuery) throws DatabaseException
	{
		int iAutoIncrement = -1;
		if (log.isDebugEnabled())
		{
			log.debug("performing query: " + sQuery);
		}

		Statement statement = null;

		try
		{
			statement = connection.createStatement();
			statement.executeUpdate(sQuery, Statement.RETURN_GENERATED_KEYS);

			ResultSet rs = statement.getGeneratedKeys();
			if (rs.next())
			{
				iAutoIncrement = rs.getInt(1);
			}

			statement.close();
		} catch (SQLException e)
		{
			throw new DatabaseException(e.getMessage());
		}

		return iAutoIncrement;
	}

	public List<Map<String, String>> query(String sQuery,
			List<DatabaseColumn> lstColumns) throws DatabaseException
	{
		if (log.isDebugEnabled())
		{
			log.debug("performing query: " + sQuery);
		}

		Statement statement = null;
		List<Map<String, String>> lstResults = new ArrayList<Map<String, String>>();

		try
		{
			statement = connection.createStatement();
			ResultSet rs = statement.executeQuery(sQuery);

			while (rs.next())
			{
				Map<String, String> mpRow = new HashMap<String, String>();
				for (DatabaseColumn column : lstColumns)
				{
					String sColumn = column.getName();
					mpRow.put(sColumn, String.valueOf(rs.getObject(sColumn)));
				}
				lstResults.add(mpRow);
			}

			statement.close();
		} catch (SQLException e)
		{
			throw new DatabaseException(e.getMessage());
		}

		return lstResults;
	}
}
